﻿using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using System.Drawing;
using System.IO;
using System.Linq;
using System.Xml;
using System.Text;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

using Microsoft.AnalysisServices.AdomdClient;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.Utilities;
using System.Xml.Linq;

namespace KoPages.CONTROLTEMPLATES.Ko
{

    public partial class Plan_BI : UserControl
    {

        #region Members

        DataTable tblShops = new DataTable();
        DataTable tblBIShops = new DataTable();

        Decimal t_1_1 = 0, t_1_2 = 0, t_1_3 = 0, t_1_4 = 0;
        Decimal t_2_1 = 0, t_2_2 = 0, t_2_3 = 0, t_2_4 = 0;
        Decimal t_3_1 = 0, t_3_2 = 0, t_3_3 = 0, t_3_4 = 0;
        Decimal t_4_1 = 0, t_4_2 = 0, t_4_3 = 0, t_4_4 = 0;
        Decimal t_5_1 = 0, t_5_2 = 0, t_5_3 = 0, t_5_4 = 0;
        Decimal t_6_1 = 0, t_6_2 = 0, t_6_3 = 0, t_6_4 = 0;
        Decimal t_7_1 = 0, t_7_2 = 0, t_7_3 = 0, t_7_4 = 0;
        Decimal t_8_1 = 0, t_8_2 = 0, t_8_3 = 0, t_8_4 = 0;

        int count_shops = 0;

        bool bRefreshLock = false;

        #endregion

        #region Common
        #region App Settings
        static public string GetAppSetting(String mName)
        {
            String vStr = null;
            SPWebService mSer = SPWebService.ContentService;
            #region Ver 1
            if (mSer != null)
            {
                foreach (SPWebConfigModification mMod in mSer.WebConfigModifications)
                {
                    if (mMod.Owner == mName)
                        vStr = mMod.Value;
                }
            }
            if (vStr != null)
            {
                vStr =
                    vStr.Substring(
                        vStr.IndexOf("value=") + 7
                        , vStr.Length - 7 - vStr.IndexOf("value=") - 4);
            }
            #endregion Ver 1
            else
            #region Ver 2
            {
                string strPath =
@"C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config";
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(strPath);
                XmlNode node = xmlDoc.SelectSingleNode(@"/configuration/appSettings");
                foreach (XmlNode keynode in node.ChildNodes)
                {
                    string keyStr = keynode.Attributes["key"].Value;
                    if (keyStr == mName)
                    {
                        vStr = keynode.Attributes["value"].Value;
                        break;
                    }
                }
            }
            #endregion Ver 2
            if (String.IsNullOrEmpty(vStr))
                vStr = "";
            return vStr;
        }
        #endregion App Settings
        public string MakeValueStr(Decimal v)
        {
            string ss = v.ToString("C");
            return ss;
        }
        private bool FindShop(string str)
        {
            bool ret = false;
            if (tblShops == null || tblBIShops == null)
                return false;
            foreach (DataRow r in tblShops.Rows)
            {
                if ((string)r["NAME"] == str)
                {
                    foreach (DataRow rr in tblBIShops.Rows)
                    {
                        if ((string)rr["NAME"] == str)
                        {
                            ret = true;
                            break;
                        }
                    }
                }
            }
            return ret;
        }
        #endregion

        #region Page Events

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DateTime dt = DateTime.Now;
                int Year = dt.Year;
                int Month = dt.Month;
                oData.DataBind();
                string strTmp = Year.ToString() + "-" + Month.ToString();
                bRefreshLock = true;
                oData.SelectedValue = strTmp;
                bRefreshLock = false;
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    Refresh();
                });
            }
        }

        protected void btnGo_Click(object sender, EventArgs e)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                Refresh();
            });
        }

        protected void oData_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (bRefreshLock == false)
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    Refresh();
                });
            }
        }

        private void Refresh()
        {
            #region Members
            StringBuilder s = new StringBuilder();
            string dateStrTmp = oData.SelectedValue;
            int Year = Convert.ToInt32(dateStrTmp.Substring(0, 4));
            int Mont = Convert.ToInt32(dateStrTmp.Substring(5));
            string connStr = GetAppSetting(@"BIConnect");
            DataTable tbl = null;
            #endregion Members

            Exception err1 = DB_BI_Plan.Get(ref tbl, connStr, Year, Mont);
            Exception err2 = GetShopsTbl();
            if (err1 != null)
            {
                Response.Write(err1.Message);
                return;
            }
            if (err2 != null)
            {
                Response.Write(err2.Message);
                return;
            }

            MakeTitle(ref s);

            foreach (DataRow r in tbl.Rows)
            {
                DataRow rr = r;
                if (FindShop((string)rr["name"]) == true)
                {
                    count_shops++;
                    MakeRow(ref s, ref rr, Year, Mont);
                }
            }

            MakeBottom(ref s);

            oMain.Text = s.ToString();

            lblBottom.Text = "Магазинов - " + count_shops;
        }
        
        #endregion

        #region Data

        private Exception GetShopsTbl()
        {
            Exception ret = null;
            List<string> errList = new List<string>();
            string connStr = GetAppSetting("DB04Connect");
            string cmdStr =
@"SELECT NAME FROM dbo.vw_a_shops ORDER BY NAME";

            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                try
                {
                    conn.Open();
                    using (OleDbDataAdapter a = new OleDbDataAdapter(cmdStr, conn))
                    {
                        a.Fill(tblShops);
                    }
                }
                catch (Exception err)
                {
                    ret = new Exception(err.Message, err);
                }
            }

            tblBIShops = new DataTable();
            ret = new WS_Helper().GetBI_Shops(ref tblBIShops, ref errList);

            return ret;
        }

        #endregion

        #region Make HTML
        void MakeTitle(ref StringBuilder s)
        {
            s.Append(
    @"<table style='width: 180px; font-family: Verdana; border-width: thin;'>
        <tr style='background-color: #003366; font-size: Small; color: #FFFFFF; font-size: X-Small;'>
            <td width='150px' style='color: #FFFF00'>
                Отдел/Магазин</td>
            <td>
                Игрушки </td>
            <td>
                Мебель </td>
            <td>
                Одежда </td>
            <td>
                Косметика </td>
            <td>
                Подгузники </td>
            <td>
                Дет. питание</td>
            <td>&nbsp;
                Обувь</td>
            <td width='75px' style='color: #FFFF00'>&nbsp;
                Итого</td>
        </tr>");
        }
        void MakeBottom(ref StringBuilder s)
        {
            MakeCellLast2(ref s);
            s.Append(@"</table>");
        }
        void MakeRow(ref StringBuilder s, ref DataRow r, int Year, int Month)
        {

            string ShopName = (string)r["name"];
            string connStr = GetAppSetting(@"BIConnect");
            MakeCellFirst(ref s, ShopName);

            // Игрушки
            Decimal v_1_1 = (Decimal)r["cell_1_1"]; t_1_1 += v_1_1;
            Decimal v_1_2 = (Decimal)r["cell_1_2"]; t_1_2 += v_1_2;
            Decimal v_1_3 = (Decimal)r["cell_1_3"]; t_1_3 += v_1_3;
            Decimal v_1_4 = (Decimal)r["cell_1_4"]; t_1_4 += v_1_4;
            MakeCell(ref s, v_1_1, v_1_2, v_1_3, v_1_4 * 100);
            // Мебель
            Decimal v_2_1 = (Decimal)r["cell_2_1"]; t_2_1 += v_2_1;
            Decimal v_2_2 = (Decimal)r["cell_2_2"]; t_2_2 += v_2_2;
            Decimal v_2_3 = (Decimal)r["cell_2_3"]; t_2_3 += v_2_3;
            Decimal v_2_4 = (Decimal)r["cell_2_4"]; t_2_4 += v_2_4;
            MakeCell(ref s, v_2_1, v_2_2, v_2_3, v_2_4 * 100);
            // Одежда
            Decimal v_3_1 = (Decimal)r["cell_3_1"]; t_3_1 += v_3_1;
            Decimal v_3_2 = (Decimal)r["cell_3_2"]; t_3_2 += v_3_2;
            Decimal v_3_3 = (Decimal)r["cell_3_3"]; t_3_3 += v_3_3;
            Decimal v_3_4 = (Decimal)r["cell_3_4"]; t_3_4 += v_3_4;
            MakeCell(ref s, v_3_1, v_3_2, v_3_3, v_3_4 * 100);
            // Косметика
            Decimal v_4_1 = (Decimal)r["cell_4_1"]; t_4_1 += v_4_1;
            Decimal v_4_2 = (Decimal)r["cell_4_2"]; t_4_2 += v_4_2;
            Decimal v_4_3 = (Decimal)r["cell_4_3"]; t_4_3 += v_4_3;
            Decimal v_4_4 = (Decimal)r["cell_4_4"]; t_4_4 += v_4_4;
            MakeCell(ref s, v_4_1, v_4_2, v_4_3, v_4_4 * 100);
            // Подгузники
            Decimal v_5_1 = (Decimal)r["cell_5_1"]; t_5_1 += v_5_1;
            Decimal v_5_2 = (Decimal)r["cell_5_2"]; t_5_2 += v_5_2;
            Decimal v_5_3 = (Decimal)r["cell_5_3"]; t_5_3 += v_5_3;
            Decimal v_5_4 = (Decimal)r["cell_5_4"]; t_5_4 += v_5_4;
            MakeCell(ref s, v_5_1, v_5_2, v_5_3, v_5_4 * 100);
            // Детское питание
            Decimal v_6_1 = (Decimal)r["cell_6_1"]; t_6_1 += v_6_1;
            Decimal v_6_2 = (Decimal)r["cell_6_2"]; t_6_2 += v_6_2;
            Decimal v_6_3 = (Decimal)r["cell_6_3"]; t_6_3 += v_6_3;
            Decimal v_6_4 = (Decimal)r["cell_6_4"]; t_6_4 += v_6_4;
            MakeCell(ref s, v_6_1, v_6_2, v_6_3, v_6_4 * 100);
            // Обувь
            Decimal v_7_1 = (Decimal)r["cell_7_1"]; t_7_1 += v_7_1;
            Decimal v_7_2 = (Decimal)r["cell_7_2"]; t_7_2 += v_7_2;
            Decimal v_7_3 = (Decimal)r["cell_7_3"]; t_7_3 += v_7_3;
            Decimal v_7_4 = (Decimal)r["cell_7_4"]; t_7_4 += v_7_4;
            MakeCell(ref s, v_7_1, v_7_2, v_7_3, v_7_4 * 100);
            
            // Итого
            DataTable tblItogoTmp = new DataTable();
            DB_BI_Plan.Get4(ref tblItogoTmp, connStr, ShopName, Year, Month);
            Decimal v_t_1 = (Decimal)tblItogoTmp.Rows[0]["cell_1_1"]; t_8_1 += v_t_1;
            Decimal v_t_2 = (Decimal)tblItogoTmp.Rows[0]["cell_1_2"]; t_8_2 += v_t_2;
            Decimal v_t_3 = (Decimal)tblItogoTmp.Rows[0]["cell_1_3"]; t_8_3 += v_t_3;
            Decimal v_t_4 = ((Decimal)(tblItogoTmp.Rows[0]["cell_1_4"]))*100; t_8_4 += v_t_4;
            MakeCellLast(ref s, v_t_1, v_t_2, v_t_3, v_t_4);

        }
        void MakeCellFirst(ref StringBuilder s, string parShopName)
        {
            s.Append(
    @"
<tr>
    <td>
        <table  width='220px' cellpadding='0' cellspacing='0' class='width: 220px;'>
            <tr style='border-style: ridge; border-width: thin'>
                <td width='100px' rowspan='4' style='font-family: Verdana; border-style: ridge; border-width: thin; background-color: #a5cbf7; color: #000000; font-size: X-Small; font-weight: bold;' >&nbsp;");

            s.Append(parShopName);

            s.Append(
    @"</td>
    <td width='80px' style='font-family: Verdana; border-style: ridge; border-width: thin; background-color: #FFFFD2; font-size: XX-Small;'>
        &nbsp;Оборот, Руб
    </td>
</tr>
<tr style='font-family: Verdana; border-style: ridge; border-width: thin; background-color: #FFFFD2; font-size: XX-Small;'>
    <td width='80px' style='border-style: none; background-color: #FFFFD2'>
        &nbsp;План на месяц
    </td>
</tr>
<tr style='font-family: Verdana; border-style: ridge; border-width: thin'>
    <td width='80px' style='border-style: ridge; border-width: thin; background-color: #FFFFD2; font-size: XX-Small;'>
        &nbsp;Прогноз, руб</td>
</tr>
<tr style='font-family: Verdana; border-style: ridge; border-width: thin'>
    <td width='80px' style='border-style: ridge; border-width: thin; background-color: #FFFFD2; font-size: XX-Small;'>
        &nbsp;Отклонение, %</td>
</tr>
</table>
</td>
");
        }
        void MakeCell(ref StringBuilder s, Decimal parReal, Decimal parPlan, Decimal parPrognoz, Decimal parProcent)
        {
            s.Append(@"
<td>
<table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;'>");
            #region Итго
            s.Append(@"
<tr align='right' valign='top' Style='font-size: X-Small; font-weight: normal; color: #0000FF;border-width: thin;' >
    <td width='40px' align='right' valign='top' >");
            s.Append("<div title=''>");
            //s.Append(string.Format("{0:C}</div>", parReal));
            s.Append(string.Format("{0:###,###,###,###,###}</div>", parReal));
            #endregion Итго
            #region План
            s.Append(
    @"</td></tr>
<tr align='right' valign='top' Style='font-size: X-Small; font-weight: normal; color: #000000;border-width: thin;' >
    <td width='40px' align='right' valign='top' >&nbsp;");
            //s.Append(string.Format("{0:C}", parPlan));
            s.Append(string.Format("{0:###,###,###,###,###}", parPlan));
            #endregion План
            #region Прогноз
            s.Append(
    @"</td></tr>
<tr align='right' valign='top' Style='font-size: X-Small; font-weight: normal; color: #339933;border-width: thin;' >
    <td width='40px' align='right' valign='top' >&nbsp;");
            //s.Append(string.Format("{0:C}", parPrognoz));
            s.Append(string.Format("{0:###,###,###,###,###}", parPrognoz));
            #endregion Прогноз
            #region Опережение
            string clr = "";
            if (parProcent < 0)
                clr = "; color: #CC3300; ";
            s.Append(
    @"</td></tr>
<tr align='right' valign='top' Style='font-size: X-Small; font-weight: normal; color: #000099;border-width: thin;'>
    <td width='40px' align='right' valign='top' style='border-width: thin;border-bottom-style: solid; border-right-style: solid" + clr + "'>&nbsp;");
            //s.Append(string.Format("{0:0.00}", parProcent));
            s.Append(string.Format("{0:0.00}", parProcent));
            s.Append(@"</td></tr>");
            #endregion Опережение
            s.Append(@"</table></td>");
        }
        void MakeCellLast(ref StringBuilder s, Decimal parReal, Decimal parPlan, Decimal parPrognoz, Decimal parProcent)
        {
            #region Итого
            s.Append(@"
<td><table cellpadding='0' cellspacing='0' Style='width: 75px;border-color: #3366CC;background-color: #FFFFCC;'>");
            #region Итго
            s.Append(
    @"<tr align='right' valign='top' Style='font-size: X-Small; font-weight: normal; color: #0000FF;' >
    <td width='75px' align='right' valign='top' >");
            //s.Append(string.Format("{0:C}", parReal));
            s.Append(string.Format("{0:###,###,###,###,###}", parReal));
            #endregion Итго
            #region План
            s.Append(
    @"</td></tr>
    <tr align='right' valign='top' Style='font-size: X-Small; font-weight: normal; color: #000000;' >
        <td width='75px' align='right' valign='top' >&nbsp;");
            //s.Append(string.Format("{0:C}", parPlan));
            s.Append(string.Format("{0:###,###,###,###,###}", parPlan));
            #endregion План
            #region Прогноз
            s.Append(
    @"</td></tr>
    <tr align='right' valign='top' Style='font-size: X-Small; font-weight: normal; color: #339933;' >
        <td width='75px' align='right' valign='top' >&nbsp;");
            //s.Append(string.Format("{0:C}", parPrognoz));
            s.Append(string.Format("{0:###,###,###,###,###}", parPrognoz));
            #endregion Прогноз
            #region Опережение
            string clr = "";
            if (parProcent < 0)
                clr = "; color: #CC3300; ";
            s.Append(
    @"</td></tr>
    <tr align='right' valign='top' Style='font-size: X-Small; font-weight: normal; color: #000099;'>
        <td width='75px' align='right' valign='top' style='border-width: thin; border-bottom-style: solid; border-right-style: solid;" + clr + "'>&nbsp;");
            s.Append(string.Format("{0:0.00}", parProcent));
            s.Append(@"</td></tr>");
            #endregion Опережение
            s.Append(@"</table></td>");
            #endregion Итого
            s.Append(@"<td>&nbsp;</td></tr>");
        }
        void MakeCell2(ref StringBuilder s, Decimal parReal, Decimal parPlan, Decimal parPrognoz, Decimal parProcent)
        {
            s.Append(@"
<td><table cellpadding='0' cellspacing='0' Style='font-size: X-Small; width: 150px%; border-color: #3366CC;background-color: #FFFFCC;'>");
            #region Итго
            s.Append(
    @"<tr align='right' valign='top' Style='font-size: X-Small; font-weight: normal; color: #0000FF;' >
    <td align='right' valign='top' >");
            //s.Append(string.Format("{0:C}", parReal));
            s.Append(string.Format("{0:###,###,###,###,###}", parReal));
            #endregion Итго
            #region План
            s.Append(
    @"</td></tr>
    <tr align='right' valign='top' Style='font-size: X-Small; font-weight: normal; color: #000000;' >
        <td align='right' valign='top' >&nbsp;");
            //s.Append(string.Format("{0:C}", parPlan));
            s.Append(string.Format("{0:###,###,###,###,###}", parPlan));
            #endregion План
            #region Прогноз
            s.Append(
    @"</td></tr>
    <tr align='right' valign='top' Style='font-size: X-Small; font-weight: normal; color: #339933;' >
        <td align='right' valign='top' >&nbsp;");
            //s.Append(string.Format("{0:C}", parPrognoz));
            s.Append(string.Format("{0:###,###,###,###,###}", parPrognoz));
            #endregion Прогноз
            #region Опережение
            string clr = "";
            if (parProcent < 0)
                clr = "; color: #CC3300; ";
            s.Append(
    @"</td></tr>
    <tr align='right' valign='top' Style='font-size: X-Small; font-weight: normal; color: #000099;'>
        <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr + "'>&nbsp;");
            s.Append(string.Format("{0:0.00}", parProcent));
            s.Append(@"</td></tr>");
            #endregion Опережение
            s.Append(@"</table></td>");
        }
        void MakeCellLast2(ref StringBuilder s)
        {
            DataTable tbl = new DataTable();
            DataTable tblTotal = new DataTable();
            
            string dateStrTmp = oData.SelectedValue;
            int Year = Convert.ToInt32(dateStrTmp.Substring(0, 4));
            int Mont = Convert.ToInt32(dateStrTmp.Substring(5));
            string connStr = GetAppSetting(@"BIConnect");

            DB_BI_Plan.Get2(ref tblTotal, connStr, Year, Mont);
            DB_BI_Plan.Get3(ref tbl, connStr, Year, Mont);

            s.Append(@"<tr><td></td>");
            // Игрушки
            t_1_1 = Convert.ToDecimal(tbl.Rows[0]["cell_2_3"]);
            t_1_2 = Convert.ToDecimal(tbl.Rows[0]["cell_2_4"]);
            t_1_3 = Convert.ToDecimal(tbl.Rows[0]["cell_2_1"]);
            t_1_4 = Convert.ToDecimal(tbl.Rows[0]["cell_2_2"]);
            MakeCell2(ref s, t_1_1, t_1_2, t_1_3, t_1_4 * 100);
            // Мебель
            t_2_1 = Convert.ToDecimal(tbl.Rows[0]["cell_4_3"]);
            t_2_2 = Convert.ToDecimal(tbl.Rows[0]["cell_4_4"]);
            t_2_3 = Convert.ToDecimal(tbl.Rows[0]["cell_4_1"]);
            t_2_4 = Convert.ToDecimal(tbl.Rows[0]["cell_4_2"]);
            MakeCell2(ref s, t_2_1, t_2_2, t_2_3, t_2_4 * 100);
            // Одежда
            t_3_1 = Convert.ToDecimal(tbl.Rows[0]["cell_6_3"]);
            t_3_2 = Convert.ToDecimal(tbl.Rows[0]["cell_6_4"]);
            t_3_3 = Convert.ToDecimal(tbl.Rows[0]["cell_6_1"]);
            t_3_4 = Convert.ToDecimal(tbl.Rows[0]["cell_6_2"]);
            MakeCell2(ref s, t_3_1, t_3_2, t_3_3, t_3_4 * 100);
            // Косметика
            t_4_1 = Convert.ToDecimal(tbl.Rows[0]["cell_3_3"]);
            t_4_2 = Convert.ToDecimal(tbl.Rows[0]["cell_3_4"]);
            t_4_3 = Convert.ToDecimal(tbl.Rows[0]["cell_3_1"]);
            t_4_4 = Convert.ToDecimal(tbl.Rows[0]["cell_3_2"]);
            MakeCell2(ref s, t_4_1, t_4_2, t_4_3, t_4_4 * 100);
            // Подгузники
            t_5_1 = Convert.ToDecimal(tbl.Rows[0]["cell_7_3"]);
            t_5_2 = Convert.ToDecimal(tbl.Rows[0]["cell_7_4"]);
            t_5_3 = Convert.ToDecimal(tbl.Rows[0]["cell_7_1"]);
            t_5_4 = Convert.ToDecimal(tbl.Rows[0]["cell_7_2"]);
            MakeCell2(ref s, t_5_1, t_5_2, t_5_3, t_5_4 * 100);
            // Детское питание
            t_6_1 = Convert.ToDecimal(tbl.Rows[0]["cell_1_3"]);
            t_6_2 = Convert.ToDecimal(tbl.Rows[0]["cell_1_4"]);
            t_6_3 = Convert.ToDecimal(tbl.Rows[0]["cell_1_1"]);
            t_6_4 = Convert.ToDecimal(tbl.Rows[0]["cell_1_2"]);
            MakeCell2(ref s, t_6_1, t_6_2, t_6_3, t_6_4 * 100);
            // Обувь
            t_7_1 = Convert.ToDecimal(tbl.Rows[0]["cell_5_3"]);
            t_7_2 = Convert.ToDecimal(tbl.Rows[0]["cell_5_4"]);
            t_7_3 = Convert.ToDecimal(tbl.Rows[0]["cell_5_1"]);
            t_7_4 = Convert.ToDecimal(tbl.Rows[0]["cell_5_2"]);
            MakeCell2(ref s, t_7_1, t_7_2, t_7_3, t_7_4 * 100);

            t_8_1 = Convert.ToDecimal(tblTotal.Rows[0]["cell_1"]);
            t_8_2 = Convert.ToDecimal(tblTotal.Rows[0]["cell_2"]);
            t_8_3 = Convert.ToDecimal(tblTotal.Rows[0]["cell_3"]);
            t_8_4 = Convert.ToDecimal(tblTotal.Rows[0]["cell_4"]);
            MakeCell2(ref s, t_8_1, t_8_2, t_8_3, t_8_4 * 100);

            s.Append(@"<td>&nbsp;</td></tr>");
        }
        #endregion Make HTML

    }

    public class DB_BI_Plan
    {

        #region Members

        #endregion

        #region Constructors
        DB_BI_Plan()
        {
        }
        #endregion

        public static Exception Get(ref DataTable tbl, string parConnStr, int parYear, int parMonth)
        {
            Exception ret = null;
            CellSet cst = null;
            string cmdStr1 =
@"
SELECT
HIERARCHIZE( { [Дата].[Месяцы].&[";
            string cmdStr2 =
@"] } ) * 
( { 
      [Номенклатура].[Секция].&[ИГРУШКИ]
    , [Номенклатура].[Секция].&[МЕБЕЛЬ]
    , [Номенклатура].[Секция].&[ОДЕЖДА]
    , [Номенклатура].[Секция].&[КОСМЕТИКА]
    , [Номенклатура].[Секция].&[ПОДГУЗНИКИ] 
    , [Номенклатура].[Секция].&[ДЕТСКОЕ ПИТАНИЕ]
    , [Номенклатура].[Секция].&[ОБУВЬ]
} )
ON COLUMNS,
{
    [Подразделение].[Подразделение].[Подразделение].ALLMEMBERS } 
    * 
    { 
          [Measures].[Оборот7 Руб]
        , [Measures].[План на месяц]
        , [Measures].[Прогноз7 руб]
        , [Measures].[Откл-е прогноза %] 
}
ON ROWS
FROM [KPI]
CELL PROPERTIES VALUE
, FORMATTED_VALUE
, CELL_ORDINAL
, FONT_FLAGS
, FORE_COLOR
, BACK_COLOR
";

            string datastr = string.Format("{0,4:0000}{1,2:00}", parYear, parMonth);
            string cmdStr = cmdStr1 + datastr + cmdStr2;

            Exception err = Exec(ref cst, parConnStr, cmdStr);

            if (err != null)
            {
                ret = new Exception(err.Message);
            }
            else
            {
                err = Parse(ref cst, ref tbl);
            }

            return ret;
        }

        public static Exception Get2(ref DataTable tbl, string parConnStr, int parYear, int parMonth)
        {
            Exception ret = null;
            CellSet cst = null;

            string cmdStr1 =
@"
SELECT
{
      [Measures].[Оборот7 Руб]
    , [Measures].[План на месяц]
    , [Measures].[Прогноз7 руб]
    , [Measures].[Откл-е прогноза %]
}
ON COLUMNS,
DISTINCT( HIERARCHIZE( { EXCEPT( { EXCEPT( { 
[Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr2 =
@"], [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr3 =
@"].CHILDREN }, { DESCENDANTS( 
[Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr4 =
@"], [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr5 =
@"].LEVEL, AFTER )
} ), [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr6 =
@"].CHILDREN }, 
{ DESCENDANTS( [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr7 =
@"]
, [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr8 =
@"].LEVEL, AFTER ) } ) } ) )
ON ROWS
FROM [KPI]
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR
";

            string datastr = string.Format("{0,4:0000}{1,2:00}", parYear, parMonth);
            string cmdStr = cmdStr1 + datastr + cmdStr2 + datastr + cmdStr3 + datastr + cmdStr4 + datastr + cmdStr5 + datastr + cmdStr6 + datastr + cmdStr7 + datastr + cmdStr8;

            Exception err = Exec(ref cst, parConnStr, cmdStr);

            if (err != null)
            {
                ret = new Exception(err.Message);
            }
            else
            {
                err = Parse2(ref cst, ref tbl);
            }

            return ret;
        }

        public static Exception Get3(ref DataTable tbl, string parConnStr, int parYear, int parMonth)
        {
            Exception ret = null;
            CellSet cst = null;

            string cmdStr1 =
@"
SELECT
HIERARCHIZE( { 
      [Номенклатура].[Секция].&[ДЕТСКОЕ ПИТАНИЕ]
    , [Номенклатура].[Секция].&[ИГРУШКИ]
    , [Номенклатура].[Секция].&[КОСМЕТИКА]
    , [Номенклатура].[Секция].&[МЕБЕЛЬ]
    , [Номенклатура].[Секция].&[ОБУВЬ]
    , [Номенклатура].[Секция].&[ОДЕЖДА]
    , [Номенклатура].[Секция].&[ПОДГУЗНИКИ] } ) 
    * { [Measures].[Прогноз7 руб], [Measures].[Откл-е прогноза %], [Measures].[Оборот7 Руб], [Measures].[План на месяц] 
}
ON COLUMNS,
DISTINCT( HIERARCHIZE( { EXCEPT( { EXCEPT( { EXCEPT( { EXCEPT( 
{ [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr2 =
@"], [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr3 =
@"].CHILDREN }, { DESCENDANTS( [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr4 =
@"], [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr5 =
@"].LEVEL, AFTER ) } ), [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr6 =
@"].CHILDREN }, { DESCENDANTS( [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr7 =
@"], [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr8 =
@"].LEVEL, AFTER ) } ), [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr9 =
@"].CHILDREN }, { DESCENDANTS( [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr10 =
@"], [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr11 =
@"].LEVEL, AFTER ) } ), [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr12 =
@"].CHILDREN }, { DESCENDANTS( [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr13 =
@"], [Дата].[Г-М-Д].[Месяц].&[";
            string cmdStr14 =
@"].LEVEL, AFTER ) } ) } ) )
ON ROWS
FROM [KPI]
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR
";

            string datastr = string.Format("{0,4:0000}{1,2:00}", parYear, parMonth);
            string cmdStr = cmdStr1 + datastr + cmdStr2 + datastr + cmdStr3 + datastr + cmdStr4 + datastr + cmdStr5 + datastr + cmdStr6 + datastr + cmdStr7 + datastr + cmdStr8 + datastr + cmdStr9 + datastr + cmdStr10 + datastr + cmdStr11 + datastr + cmdStr12 + datastr + cmdStr13 + datastr + cmdStr14;

            Exception err = Exec(ref cst, parConnStr, cmdStr);

            if (err != null)
            {
                ret = new Exception(err.Message);
            }
            else
            {
                err = Parse3(ref cst, ref tbl);
            }

            return ret;
        }

        public static Exception Get4(ref DataTable tbl, string parConnStr, string parShopName, int parYear, int parMonth)
        {
            Exception ret = null;
            CellSet cst = null;
            /*
            SELECT
            HIERARCHIZE( { [Дата].[Месяцы].&[201111] } ) 
            ON COLUMNS,
            {
                [Подразделение].[Подразделение].[Подразделение].[Александров] } 
                * 
                { 
                        [Measures].[Оборот7 Руб]
                    , [Measures].[План на месяц]
                    , [Measures].[Прогноз7 руб]
                    , [Measures].[Откл-е прогноза %] 
            }
            ON ROWS
            FROM [KPI] 
            CELL PROPERTIES VALUE
            , FORMATTED_VALUE
            , CELL_ORDINAL
            , FONT_FLAGS
            , FORE_COLOR
            , BACK_COLOR
            */
            string cmdStr1 =
@"
SELECT
HIERARCHIZE( { [Дата].[Месяцы].&[";
            string cmdStr2 =
@"] } ) 
ON COLUMNS,
{
    [Подразделение].[Подразделение].[Подразделение].[";
            string cmdStr3 =
@"] } 
    * 
    { 
          [Measures].[Оборот7 Руб]
        , [Measures].[План на месяц]
        , [Measures].[Прогноз7 руб]
        , [Measures].[Откл-е прогноза %] 
}
ON ROWS
FROM [KPI]
CELL PROPERTIES VALUE
, FORMATTED_VALUE
, CELL_ORDINAL
, FONT_FLAGS
, FORE_COLOR
, BACK_COLOR
";

            string datastr = string.Format("{0,4:0000}{1,2:00}", parYear, parMonth);
            string cmdStr = cmdStr1 + datastr + cmdStr2 + parShopName + cmdStr3;

            Exception err = Exec(ref cst, parConnStr, cmdStr);

            if (err != null)
            {
                ret = new Exception(err.Message);
            }
            else
            {
                err = Parse4(ref cst, ref tbl);
            }

            return ret;
        }

        public static Exception Exec(ref CellSet parCst, string parConnStr, string parCmdStr)
        {
            Exception ret = null;
            try
            {
                using (AdomdConnection conn = new AdomdConnection(parConnStr))
                {
                    conn.Open();
                    using (AdomdCommand cmd = new AdomdCommand(parCmdStr, conn))
                    {
                        parCst = cmd.ExecuteCellSet();
                        conn.Close();
                    }
                }
            }
            catch (Exception err)
            {
                parCst = null;
                ret = new Exception(err.Message, err);
            }

            return ret;
        }

        public static Exception Parse(ref CellSet parCst, ref DataTable parTbl)
        {
            Exception ret = null;

            MakeTbl(ref parTbl);

            // check if any axes were returned else throw error.
            int axes_count = parCst.Axes.Count;
            if (axes_count == 0)
                return new Exception("No data returned for the selection");

            // if axes count is not 2
            if (axes_count != 2)
                return new Exception("The sample code support only queries with two axes");

            // if no position on either row or column throw error
            if (!(parCst.Axes[0].Positions.Count > 0) && !(parCst.Axes[1].Positions.Count > 0))
                return new Exception("No data returned for the selection");

            #region Members

            int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
            cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

            // Number of dimensions on the column
            col_dim_count = parCst.Axes[0].Positions[0].Members.Count;

            // Number of dimensions on the row
            if (parCst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

            // Total rows and columns
            row_count = parCst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
            col_count = parCst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

            int count = 0;
            DataRow r = null;

            #endregion

            for (cur_row = 0; cur_row < row_count - 2; cur_row++)
            {

                count++;
                if (count >= 5)
                    count = 1;
                if (count == 1)
                    r = parTbl.NewRow();

                string strShopName = parCst.Axes[1].Positions[cur_row].Members[0].Caption;
                string strValueType = parCst.Axes[1].Positions[cur_row].Members[1].Caption;
                string strMonth = "";

                try
                {
                    strMonth = parCst.Axes[0].Positions[cur_row].Members[0].Caption;
                }
                catch
                {
                }

                #region Data

                Decimal val1 = Convert.ToDecimal(parCst[0, cur_row].Value);
                Decimal val2 = Convert.ToDecimal(parCst[1, cur_row].Value);
                Decimal val3 = Convert.ToDecimal(parCst[2, cur_row].Value);
                Decimal val4 = Convert.ToDecimal(parCst[3, cur_row].Value);
                Decimal val5 = Convert.ToDecimal(parCst[4, cur_row].Value);
                Decimal val6 = Convert.ToDecimal(parCst[5, cur_row].Value);
                Decimal val7 = Convert.ToDecimal(parCst[6, cur_row].Value);

                if (count == 1)
                {
                    r["name"] = strShopName;
                    r["cell_1_1"] = val1;
                    r["cell_2_1"] = val2;
                    r["cell_3_1"] = val3;
                    r["cell_4_1"] = val4;
                    r["cell_5_1"] = val5;
                    r["cell_6_1"] = val6;
                    r["cell_7_1"] = val7;
                    r["cell_t_1"] = val1 + val2 + val3 + val4 + val5 + val6 + val7;
                }

                if (count == 2)
                {
                    r["name"] = strShopName;
                    r["cell_1_2"] = val1;
                    r["cell_2_2"] = val2;
                    r["cell_3_2"] = val3;
                    r["cell_4_2"] = val4;
                    r["cell_5_2"] = val5;
                    r["cell_6_2"] = val6;
                    r["cell_7_2"] = val7;
                    r["cell_t_2"] = val1 + val2 + val3 + val4 + val5 + val6 + val7;
                }

                if (count == 3)
                {
                    r["name"] = strShopName;
                    r["cell_1_3"] = val1;
                    r["cell_2_3"] = val2;
                    r["cell_3_3"] = val3;
                    r["cell_4_3"] = val4;
                    r["cell_5_3"] = val5;
                    r["cell_6_3"] = val6;
                    r["cell_7_3"] = val7;
                    r["cell_t_3"] = val1 + val2 + val3 + val4 + val5 + val6 + val7;
                }

                if (count == 4)
                {
                    r["name"] = strShopName;
                    r["cell_1_4"] = val1;
                    r["cell_2_4"] = val2;
                    r["cell_3_4"] = val3;
                    r["cell_4_4"] = val4;
                    r["cell_5_4"] = val5;
                    r["cell_6_4"] = val6;
                    r["cell_7_4"] = val7;
                    r["cell_t_4"] = val1 + val2 + val3 + val4 + val5 + val6 + val7;
                    parTbl.Rows.Add(r);
                }

                #endregion

            }

            return ret;
        }

        public static Exception Parse2(ref CellSet parCst, ref DataTable parTbl)
        {
            Exception ret = null;

            MakeTbl2(ref parTbl);

            #region Error

            // check if any axes were returned else throw error.
            int axes_count = parCst.Axes.Count;
            if (axes_count == 0)
                return new Exception("No data returned for the selection");

            // if axes count is not 2
            if (axes_count != 2)
                return new Exception("The sample code support only queries with two axes");

            // if no position on either row or column throw error
            if (!(parCst.Axes[0].Positions.Count > 0) && !(parCst.Axes[1].Positions.Count > 0))
                return new Exception("No data returned for the selection");

            #endregion

            #region Members

            int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
            cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

            // Number of dimensions on the column
            col_dim_count = parCst.Axes[0].Positions[0].Members.Count;

            // Number of dimensions on the row
            if (parCst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

            // Total rows and columns
            row_count = parCst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
            col_count = parCst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

            int count = 0;
            DataRow r = null;

            #endregion

            for (cur_row = 1; cur_row < row_count; cur_row++)
            {
                count++;
                r = parTbl.NewRow();

                Decimal val1 = Convert.ToDecimal(parCst[0, 0].Value);
                Decimal val2 = Convert.ToDecimal(parCst[1, 0].Value);
                Decimal val3 = Convert.ToDecimal(parCst[2, 0].Value);
                Decimal val4 = Convert.ToDecimal(parCst[3, 0].Value);

                r["name"] = "";
                r["cell_1"] = val1;
                r["cell_2"] = val2;
                r["cell_3"] = val3;
                r["cell_4"] = val4;

                parTbl.Rows.Add(r);
            }

            return ret;
        }

        public static Exception Parse3(ref CellSet parCst, ref DataTable parTbl)
        {
            Exception ret = null;

            MakeTbl3(ref parTbl);

            #region Error

            // check if any axes were returned else throw error.
            int axes_count = parCst.Axes.Count;
            if (axes_count == 0)
                return new Exception("No data returned for the selection");

            // if axes count is not 2
            if (axes_count != 2)
                return new Exception("The sample code support only queries with two axes");

            // if no position on either row or column throw error
            if (!(parCst.Axes[0].Positions.Count > 0) && !(parCst.Axes[1].Positions.Count > 0))
                return new Exception("No data returned for the selection");

            #endregion

            #region Members

            int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
            cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

            // Number of dimensions on the column
            col_dim_count = parCst.Axes[0].Positions[0].Members.Count;

            // Number of dimensions on the row
            if (parCst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

            // Total rows and columns
            row_count = parCst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
            col_count = parCst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

            int count = 0;
            DataRow r = null;

            #endregion

            for (cur_row = 2; cur_row < 3; cur_row++)
            {
                count++;
                r = parTbl.NewRow();

                Decimal val1_1 = Convert.ToDecimal(parCst[0, 0].Value);
                Decimal val1_2 = Convert.ToDecimal(parCst[1, 0].Value);
                Decimal val1_3 = Convert.ToDecimal(parCst[2, 0].Value);
                Decimal val1_4 = Convert.ToDecimal(parCst[3, 0].Value);

                Decimal val2_1 = Convert.ToDecimal(parCst[4, 0].Value);
                Decimal val2_2 = Convert.ToDecimal(parCst[5, 0].Value);
                Decimal val2_3 = Convert.ToDecimal(parCst[6, 0].Value);
                Decimal val2_4 = Convert.ToDecimal(parCst[7, 0].Value);

                Decimal val3_1 = Convert.ToDecimal(parCst[8, 0].Value);
                Decimal val3_2 = Convert.ToDecimal(parCst[9, 0].Value);
                Decimal val3_3 = Convert.ToDecimal(parCst[10, 0].Value);
                Decimal val3_4 = Convert.ToDecimal(parCst[11, 0].Value);

                Decimal val4_1 = Convert.ToDecimal(parCst[12, 0].Value);
                Decimal val4_2 = Convert.ToDecimal(parCst[13, 0].Value);
                Decimal val4_3 = Convert.ToDecimal(parCst[14, 0].Value);
                Decimal val4_4 = Convert.ToDecimal(parCst[15, 0].Value);

                Decimal val5_1 = Convert.ToDecimal(parCst[16, 0].Value);
                Decimal val5_2 = Convert.ToDecimal(parCst[17, 0].Value);
                Decimal val5_3 = Convert.ToDecimal(parCst[18, 0].Value);
                Decimal val5_4 = Convert.ToDecimal(parCst[19, 0].Value);

                Decimal val6_1 = Convert.ToDecimal(parCst[20, 0].Value);
                Decimal val6_2 = Convert.ToDecimal(parCst[21, 0].Value);
                Decimal val6_3 = Convert.ToDecimal(parCst[22, 0].Value);
                Decimal val6_4 = Convert.ToDecimal(parCst[23, 0].Value);

                Decimal val7_1 = Convert.ToDecimal(parCst[24, 0].Value);
                Decimal val7_2 = Convert.ToDecimal(parCst[25, 0].Value);
                Decimal val7_3 = Convert.ToDecimal(parCst[26, 0].Value);
                Decimal val7_4 = Convert.ToDecimal(parCst[27, 0].Value);

                r["name"] = "";
                r["cell_1_1"] = val1_1;
                r["cell_1_2"] = val1_2;
                r["cell_1_3"] = val1_3;
                r["cell_1_4"] = val1_4;
                r["cell_2_1"] = val2_1;
                r["cell_2_2"] = val2_2;
                r["cell_2_3"] = val2_3;
                r["cell_2_4"] = val2_4;
                r["cell_3_1"] = val3_1;
                r["cell_3_2"] = val3_2;
                r["cell_3_3"] = val3_3;
                r["cell_3_4"] = val3_4;
                r["cell_4_1"] = val4_1;
                r["cell_4_2"] = val4_2;
                r["cell_4_3"] = val4_3;
                r["cell_4_4"] = val4_4;
                r["cell_5_1"] = val5_1;
                r["cell_5_2"] = val5_2;
                r["cell_5_3"] = val5_3;
                r["cell_5_4"] = val5_4;
                r["cell_6_1"] = val6_1;
                r["cell_6_2"] = val6_2;
                r["cell_6_3"] = val6_3;
                r["cell_6_4"] = val6_4;
                r["cell_7_1"] = val7_1;
                r["cell_7_2"] = val7_2;
                r["cell_7_3"] = val7_3;
                r["cell_7_4"] = val7_4;

                parTbl.Rows.Add(r);
            }

            return ret;
        }

        public static Exception Parse4(ref CellSet parCst, ref DataTable parTbl)
        {
            Exception ret = null;

            MakeTbl(ref parTbl);

            // check if any axes were returned else throw error.
            int axes_count = parCst.Axes.Count;
            if (axes_count == 0)
                return new Exception("No data returned for the selection");

            // if axes count is not 2
            if (axes_count != 2)
                return new Exception("The sample code support only queries with two axes");

            // if no position on either row or column throw error
            if (!(parCst.Axes[0].Positions.Count > 0) && !(parCst.Axes[1].Positions.Count > 0))
                return new Exception("No data returned for the selection");

            #region Members

            int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
            cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

            // Number of dimensions on the column
            col_dim_count = parCst.Axes[0].Positions[0].Members.Count;

            // Number of dimensions on the row
            if (parCst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

            // Total rows and columns
            row_count = parCst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
            col_count = parCst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

            int count = 0;
            DataRow r = null;

            #endregion

            for (cur_row = 0; cur_row < row_count - 2; cur_row++)
            {

                count++;
                if (count >= 5)
                    count = 1;
                if (count == 1)
                    r = parTbl.NewRow();

                string strShopName = parCst.Axes[1].Positions[cur_row].Members[0].Caption;
                string strValueType = parCst.Axes[1].Positions[cur_row].Members[1].Caption;
                string strMonth = "";

                try
                {
                    strMonth = parCst.Axes[0].Positions[cur_row].Members[0].Caption;
                }
                catch
                {
                }

                #region Data

                Decimal val1 = Convert.ToDecimal(parCst[0, 0].Value);
                Decimal val2 = Convert.ToDecimal(parCst[0, 1].Value);
                Decimal val3 = Convert.ToDecimal(parCst[0, 2].Value);
                Decimal val4 = Convert.ToDecimal(parCst[0, 3].Value);

                if (count == 1)
                {
                    r["name"] = strShopName;
                    r["cell_1_1"] = val1;
                    r["cell_1_2"] = val2;
                    r["cell_1_3"] = val3;
                    r["cell_1_4"] = val4;
                    parTbl.Rows.Add(r);
                }

                break;

                #endregion

            }

            return ret;
        }

        public static void MakeTbl(ref DataTable parTbl)
        {
            if (parTbl == null)
                parTbl = new DataTable();

            parTbl.Columns.Add("name", typeof(String));

            parTbl.Columns.Add("cell_1_1", typeof(Decimal));
            parTbl.Columns.Add("cell_1_2", typeof(Decimal));
            parTbl.Columns.Add("cell_1_3", typeof(Decimal));
            parTbl.Columns.Add("cell_1_4", typeof(Decimal));

            parTbl.Columns.Add("cell_2_1", typeof(Decimal));
            parTbl.Columns.Add("cell_2_2", typeof(Decimal));
            parTbl.Columns.Add("cell_2_3", typeof(Decimal));
            parTbl.Columns.Add("cell_2_4", typeof(Decimal));

            parTbl.Columns.Add("cell_3_1", typeof(Decimal));
            parTbl.Columns.Add("cell_3_2", typeof(Decimal));
            parTbl.Columns.Add("cell_3_3", typeof(Decimal));
            parTbl.Columns.Add("cell_3_4", typeof(Decimal));

            parTbl.Columns.Add("cell_4_1", typeof(Decimal));
            parTbl.Columns.Add("cell_4_2", typeof(Decimal));
            parTbl.Columns.Add("cell_4_3", typeof(Decimal));
            parTbl.Columns.Add("cell_4_4", typeof(Decimal));

            parTbl.Columns.Add("cell_5_1", typeof(Decimal));
            parTbl.Columns.Add("cell_5_2", typeof(Decimal));
            parTbl.Columns.Add("cell_5_3", typeof(Decimal));
            parTbl.Columns.Add("cell_5_4", typeof(Decimal));

            parTbl.Columns.Add("cell_6_1", typeof(Decimal));
            parTbl.Columns.Add("cell_6_2", typeof(Decimal));
            parTbl.Columns.Add("cell_6_3", typeof(Decimal));
            parTbl.Columns.Add("cell_6_4", typeof(Decimal));

            parTbl.Columns.Add("cell_7_1", typeof(Decimal));
            parTbl.Columns.Add("cell_7_2", typeof(Decimal));
            parTbl.Columns.Add("cell_7_3", typeof(Decimal));
            parTbl.Columns.Add("cell_7_4", typeof(Decimal));

            parTbl.Columns.Add("cell_t_1", typeof(Decimal));
            parTbl.Columns.Add("cell_t_2", typeof(Decimal));
            parTbl.Columns.Add("cell_t_3", typeof(Decimal));
            parTbl.Columns.Add("cell_t_4", typeof(Decimal));


        }

        public static void MakeTbl2(ref DataTable parTbl)
        {

            if (parTbl == null)
                parTbl = new DataTable();

            parTbl.Columns.Add("name", typeof(String));

            parTbl.Columns.Add("cell_1", typeof(Decimal));
            parTbl.Columns.Add("cell_2", typeof(Decimal));
            parTbl.Columns.Add("cell_3", typeof(Decimal));
            parTbl.Columns.Add("cell_4", typeof(Decimal));

        }

        public static void MakeTbl3(ref DataTable parTbl)
        {

            if (parTbl == null)
                parTbl = new DataTable();

            parTbl.Columns.Add("name", typeof(String));

            parTbl.Columns.Add("cell_1_1", typeof(Decimal));
            parTbl.Columns.Add("cell_1_2", typeof(Decimal));
            parTbl.Columns.Add("cell_1_3", typeof(Decimal));
            parTbl.Columns.Add("cell_1_4", typeof(Decimal));
            parTbl.Columns.Add("cell_2_1", typeof(Decimal));
            parTbl.Columns.Add("cell_2_2", typeof(Decimal));
            parTbl.Columns.Add("cell_2_3", typeof(Decimal));
            parTbl.Columns.Add("cell_2_4", typeof(Decimal));
            parTbl.Columns.Add("cell_3_1", typeof(Decimal));
            parTbl.Columns.Add("cell_3_2", typeof(Decimal));
            parTbl.Columns.Add("cell_3_3", typeof(Decimal));
            parTbl.Columns.Add("cell_3_4", typeof(Decimal));
            parTbl.Columns.Add("cell_4_1", typeof(Decimal));
            parTbl.Columns.Add("cell_4_2", typeof(Decimal));
            parTbl.Columns.Add("cell_4_3", typeof(Decimal));
            parTbl.Columns.Add("cell_4_4", typeof(Decimal));
            parTbl.Columns.Add("cell_5_1", typeof(Decimal));
            parTbl.Columns.Add("cell_5_2", typeof(Decimal));
            parTbl.Columns.Add("cell_5_3", typeof(Decimal));
            parTbl.Columns.Add("cell_5_4", typeof(Decimal));
            parTbl.Columns.Add("cell_6_1", typeof(Decimal));
            parTbl.Columns.Add("cell_6_2", typeof(Decimal));
            parTbl.Columns.Add("cell_6_3", typeof(Decimal));
            parTbl.Columns.Add("cell_6_4", typeof(Decimal));
            parTbl.Columns.Add("cell_7_1", typeof(Decimal));
            parTbl.Columns.Add("cell_7_2", typeof(Decimal));
            parTbl.Columns.Add("cell_7_3", typeof(Decimal));
            parTbl.Columns.Add("cell_7_4", typeof(Decimal));
            parTbl.Columns.Add("cell_8_1", typeof(Decimal));
            parTbl.Columns.Add("cell_8_2", typeof(Decimal));
            parTbl.Columns.Add("cell_8_3", typeof(Decimal));
            parTbl.Columns.Add("cell_8_4", typeof(Decimal));

        }

    }

    public class WS_Helper
    {

        #region Common

        public Exception GetErrList(ref byte[] parData, ref List<string> parErrList)
        {
            Exception ret = null;
            try
            {
                StringBuilder sb = new StringBuilder();
                foreach (byte bt in parData)
                    sb.Append((char)bt);
                string str = sb.ToString();
                XDocument xErrors = XDocument.Parse(str);
                var query = from g in xErrors.Descendants("Errors")
                            select new
                            {
                                errName = (string)g.Element("Err")
                            };
                foreach (var node in query)
                {
                    if (node.errName == null)
                    {
                        // Errors not found
                    }
                    else
                    {
                        parErrList.Add(node.errName);
                    }
                }
            }
            catch (Exception err)
            {
                ret = new Exception(err.Message, err);
            }
            return ret;
        }

        #endregion

        #region Base Methods

        public Exception Get(ref DataTable tbl, ref List<string> errList, string UrlStr)
        {
            //string dateStrTmp = oData.SelectedValue;
            //int Year = Convert.ToInt32(dateStrTmp.Substring(0, 4));
            //int Mont = Convert.ToInt32(dateStrTmp.Substring(5));
            DateTime dt = DateTime.Now;
            dt = new DateTime(dt.Year, dt.Month, dt.Day);
            return Get(ref tbl, ref errList, UrlStr, dt.Year, dt.Month);
        }

        public Exception Get(ref DataTable tbl, ref List<string> errList, string UrlStr, int Year, int Month)
        {
            Exception ret = null;
            try
            {
                System.Net.WebClient wc = new System.Net.WebClient();
                wc.Headers["Cache-Control"] = "no-cache";
                wc.UseDefaultCredentials = false;
                wc.Credentials = new System.Net.NetworkCredential("anonymous", "janeDoe@contoso.com");
                wc.UseDefaultCredentials = false;

                Guid guid = Guid.NewGuid();
                string paramsStr = @"?guid=" + guid.ToString()
                    + @"&year=" + Year.ToString()
                    + @"&month=" + Month.ToString();

                byte[] data = wc.DownloadData(UrlStr + paramsStr);
                if (tbl == null)
                    tbl = new DataTable("tbl");
                if (data.Length > 0)
                {
                    using (System.IO.MemoryStream xmlStream = new System.IO.MemoryStream())
                    {
                        xmlStream.Write(data, 0, data.Length);
                        xmlStream.Position = 0;
                        tbl.ReadXml(xmlStream);
                        xmlStream.Position = 0;
                        if (errList == null)
                            errList = new List<string>();
                        GetErrList(ref data, ref errList);
                        if (errList.Count > 0)
                            throw new Exception(errList[0]);
                    }
                }
                else
                    throw new Exception(@"Web Service http://sp.korablik.local не вернул данных");
            }
            catch (Exception err)
            {
                ret = new Exception(err.Message, err);
            }
            return ret;
        }

        #endregion

        #region Get Catalogs

        public Exception GetSPUsersList(ref DataTable tbl, ref List<string> errList)
        {
            return Get(ref tbl, ref errList, @"http://sp.korablik.local:88/ws/Catalog/Users.aspx");
        }

        public Exception GetBI_Shops(ref DataTable tbl, ref List<string> errList)
        {
            DateTime dt = DateTime.Now;
            dt = new DateTime(dt.Year, dt.Month, dt.Day);
            return GetBI_Shops(ref tbl, ref errList, dt.Year, dt.Month);
        }

        public Exception GetBI_Shops(ref DataTable tbl, ref List<string> errList, int Year, int Month)
        {
            return Get(ref tbl, ref errList, @"http://sp.korablik.local:98/ws/Catalog/BIShops.aspx");
        }

        public Exception GetBI_ShopsAll(ref DataTable tbl, ref List<string> errList)
        {
            DateTime dt = DateTime.Now;
            dt = new DateTime(dt.Year, dt.Month, dt.Day);
            return GetBI_ShopsAll(ref tbl, ref errList, dt.Year, dt.Month);
        }

        public Exception GetBI_ShopsAll(ref DataTable tbl, ref List<string> errList, int Year, int Month)
        {
            return Get(ref tbl, ref errList, @"http://sp.korablik.local:98/ws/Catalog/BIShops_All.aspx");
        }

        #endregion

    }

}


